﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ReadRelatedEntities]
	-- Add the parameters for the stored procedure here
	@Entity1ID nvarchar(100),
	@Entity1Type nvarchar(255) = null,
	@SRNameWithDir nvarchar(100) = null,
	-- Optionally limits the search to one particular relationship and direction
	@ReadDirect int=null,
	@ReadChild int=null,
	@WriteDirect int=null,
	@WriteChild int=null,
	@SD datetime = null,
	@ED datetime = null,
	@TD nvarchar(100) = '',
	@FunctionsOnly bit = 0,
	@SelfFunctions bit = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Get list of Valid possible relationships
	-- FOR THIS PURPOSE, DOUBLE CHECK THAT THE Relationship has at least basic direct read
	DECLARE RelationshipCursor cursor for
		SELECT DISTINCT ger.* FROM GetEntityRelationships(@Entity1Type,@ReadChild, @WriteChild) ger 
			LEFT JOIN dbo.DefinedRelationshipFunctions df
			ON ger.SRNameWithDir=df.SRNameWithDir
			WHERE (
				((dbo.fn_GetRelOrDir(@SRNameWithDir,'Dir') IS NULL AND dbo.fn_GetRelOrDir(@SRNameWithDir,'Rel')=dbo.fn_GetRelOrDir(ger.SRNameWithDir,'Rel')) OR
				(@SRNameWithDir is null OR @SRNameWithDir = ger.SRNameWithDir) AND (E1_Read_Direct_E2 > 0))
					AND (@FunctionsOnly=0 OR (@FunctionsOnly=1 AND df.SRNameWithDir IS NOT NULL))
					AND ((@SelfFunctions=0 AND df.SelfFunction=0) OR @SelfFunctions=1 OR @FunctionsOnly=0)
					)

    CREATE TABLE #EntityRelationships (
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	DECLARE @NEXTREL nvarchar(100) -- THE CALLED RELATIONSHIP WITH DIRECTION
	DECLARE @DIRECTION nvarchar(15)  -- THE DIRECTION part of the CALLED RELATIONSHIP
	DECLARE @BuildSQL nvarchar(max)
	DECLARE @JSON nvarchar(255)
	DECLARE @SuperRelationship nvarchar(12) 
	-- Rights....
	DECLARE @RD int, @RC int, @WD int, @WC int
	DECLARE @E1Type nvarchar(100), @E2Type nvarchar(100), @RelName nvarchar(50), @E2SubType nvarchar(50)

	-- Walk through the list of valid Relationships:
	Open RelationshipCursor
	Fetch NEXT FROM RelationshipCursor INTO  @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type,  @RD, @RC, @WD, @WC
	WHILE @@FETCH_STATUS = 0 BEGIN

			SET @DIRECTION = dbo.fn_GetRelOrDir(@NEXTREL,'Dir')
			IF (@E2SubType is not null) BEGIN
					SET @E2Type = @E2Type + '.' + @E2SubType
			END

			SET @BuildSQL = 'SELECT Entity1ID, "' + @E1Type + '" AS Entity1Type, Entity1Name, ' +
						' SRNameWithDir, RelationshipName, ' +
						' SuperRelationship, Entity2ID, "' + @E2Type + '" + COALESCE("." + Entities.EntitySubType,"") As Entity2Type, Entity2Name,' +
						' StartDate, EndDate, ' + CAST(@RD AS NVARCHAR) + ' AS RD, ' + CAST(@RC AS NVARCHAR) +
						' AS RC, ' + CAST(@WD AS NVARCHAR) + ' AS WD, ' + CAST(@WC AS NVARCHAR) + ' AS WC ' +
						' FROM [{' + @NEXTREL + ' R} WHERE (Entity1ID = "' + @Entity1ID + '") ] ' +
						' LEFT JOIN Entities ON CAST(Entities.EntityID AS NVARCHAR)=CAST(R.Entity2ID AS NVARCHAR)'
				SET @BuildSQL = 'INSERT INTO #EntityRelationships(Entity1ID,Entity1Type,Entity1Name,
							SRNameWithDir, RelationshipName, SuperRelationship,	Entity2ID, 
							Entity2Type, Entity2Name, StartDate, EndDate, E1_ReadDirect_E2, E1_ReadChild_E2,
							E1_WriteDirect_E2, E1_WriteChild_E2) ' + @BuildSQL
				SET @JSON ='{"@GENERICENTITYID":"' + @Entity1ID+'"}'

				BEGIN TRY
					EXEC dbo.ExecuteRafikiNOSQL @NOSQL=@BuildSQL, @SD=@SD, @ED=@ED, @TD=@TD, @JSONPARAMETERS=@JSON
				END TRY
				BEGIN CATCH
				END CATCH
				Fetch NEXT FROM RelationshipCursor INTO   @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type,  @RD, @RC, @WD, @WC
	END
	CLOSE RelationshipCursor
	DEALLOCATE RelationshipCursor

	SELECT DISTINCT df.DisplayFunctionName, df.ShortFunctionName, df.ViewName, df.FunctionCommand, df.E1LinkName,
		df.E2LinkName, df.TabOrder,
		 er.* FROM #EntityRelationships er
		LEFT JOIN dbo.DefinedRelationshipFunctions df
			ON er.SRNameWithDir=df.SRNameWithDir OR
				er.SuperRelationship + '.' + dbo.fn_GetRelOrDir(er.SRNameWithDir,'Dir') = df.SRNameWithDir
				ORDER BY df.TabOrder
	
END